const db = require('../model/database')
const sqlQuery = require('../model/query')
const dayjs = require("dayjs")

// 获取学生疫情信息
exports.getStudentEpidemic = async (req, res, next) => {
  // 获取班级id
  const { classId } = req.query
  // 获取查询条件
  const searchObj = req.body
  // 去除空字符串
  for (let key in searchObj) {
    if (searchObj[key] === '' || searchObj[key] === null) {
      delete searchObj[key]
    }
  }
  // 获取该班级的学生
  const sql1 = 'select id from student where class = ? and is_delete = 0'
  const result1 = await sqlQuery(sql1, [classId], db)
  // 获取该班级在epidemic表中的学生id
  const sql2 = 'select student_id from epidemic where student_id in (select id from student where class = ?)'
  const result2 = await sqlQuery(sql2, [classId], db)
  // 从result1和result2中取出值
  const result1Id = result1.map(item => item.id)
  const result2Id = result2.map(item => item.student_id)
  // 比较两个数组, 找出差集
  const diff = result1Id.filter(item => !result2Id.includes(item))
  // 将差集插入到epidemic表中
  if (diff.length > 0) {
    const sql3 = 'INSERT INTO epidemic (student_id) VALUES ?'
    const values = []
    diff.forEach(item => {
      values.push([item])
    })
    await sqlQuery(sql3, [values], db)
  }

  const searchSql = res.getSearchSql(searchObj)
  // 获取学生疫情信息 多表查询 student表和epidemic表 字段分别重新命名
  const sql = `select *, s.id as student_id, s.address as student_address from student s left join epidemic e on s.id = e.student_id where s.class = ? and s.is_delete = 0 ${searchSql}`
  console.log(sql)
  const result = await sqlQuery(sql, [classId], db)
  res.status(200).json({
    code: 20000,
    message: '获取学生疫情信息成功',
    data: result
  })
}

// 初始化学生疫情信息
exports.initStudentEpidemic = async (req, res) => {
  // 获取班级id
  const { classId } = req.body
  // 删除对应班级的学生疫情信息
  const sql1 = 'delete from epidemic where student_id in (select id from student where class = ?)'
  const result1 = await sqlQuery(sql1, [classId], db)
  // 获取对应班级的学生
  const sql = 'select * from student where class = ? and is_delete = 0'
  const result = await sqlQuery(sql, [classId], db)
  // 给每个学生信息添加创建时间
  result.forEach(item => {
    item.create_time = dayjs().format('YYYY-MM-DD HH:mm:ss')
  })
  // 初始化学生疫情信息
  const sql2 = 'insert into epidemic (student_id, create_time) values (?, ?)'
  for (let i = 0; i < result.length; i++) {
    await sqlQuery(sql2, [result[i].id, result[i].create_time], db)
  }
  res.status(200).json({
    code: 20000,
    message: '初始化学生疫情信息成功',
  })
}

// 更新学生疫情信息
exports.updateStudentEpidemic = async (req, res) => {
  // 获取学生id
  const data = req.body
  // 保存学生id
  const studentId = []
  for (let i = 0; i < data.length; i++) {
    studentId.push(data[i].student_id)
    // 删除学生id
    delete data[i].student_id
    // 更新时间
    data[i].update_time = dayjs().format('YYYY-MM-DD HH:mm:ss')
  }
  // 更新学生疫情信息
  const sql = 'update epidemic set ? where student_id = ?'
  for (let i = 0; i < data.length; i++) {
    await sqlQuery(sql, [data[i], studentId[i]], db)
  }
  res.status(200).json({
    code: 20000,
    message: '更新学生疫情信息成功',
  })
}
